from flask import Blueprint, request, jsonify, render_template, send_from_directory, make_response
from .models import db, ColumnModel, Review
import pandas as pd
import os

bp = Blueprint('main', __name__)

@bp.cli.command("init-db")
def init_db():
    """初始化数据库"""
    db.drop_all()
    db.create_all()
    print("数据库已初始化")

@bp.route('/')
def index():
    return render_template('review_form.html')

@bp.route('/api/columns', methods=['GET'])
def get_columns():
    columns = ColumnModel.query.all()
    return jsonify([{'id': c.id, 'name': c.name} for c in columns])

@bp.route('/api/column/<int:column_id>', methods=['GET'])
def get_column_info(column_id):
    column = ColumnModel.query.get_or_404(column_id)
    return jsonify({
        'programName': column.program_name,
        'editor': column.editor.split('、'),
        'responsibleEditor': column.responsible_editor,
        'broadcasters': column.broadcasters.split(','),
        'director': column.director
    })

@bp.route('/api/submit', methods=['POST'])
def submit_review():
    try:
        data = request.json
        required_fields = ['columnId', 'name', 'script', 'broadcast', 'padding']
        if not all(data.get(field) for field in required_fields):
            return jsonify({'status': 'error', 'message': '缺少必填字段！'}), 400

        if not ColumnModel.query.get(data['columnId']):
            return jsonify({'status': 'error', 'message': '无效的栏目ID！'}), 404

        new_review = Review(
            column_id=data['columnId'],
            name=data['name'],
            script=data['script'],
            broadcast=data['broadcast'],
            padding=data['padding']
        )
        db.session.add(new_review)
        db.session.commit()
        return jsonify({'status': 'success'})
    except Exception as e:
        return jsonify({'status': 'error', 'message': str(e)}), 500

@bp.route('/api/export', methods=['GET'])
def export_reviews():
    try:
        df = pd.read_sql_table('reviews', db.engine)
        df.rename(columns={
            'name': '姓名',
            'script': '稿件',
            'broadcast': '播音',
            'padding': '垫乐',
            'submit_time': '提交时间'
        }, inplace=True)
        
        export_path = os.path.join(os.getcwd(), 'data', 'reviews_export.xlsx')
        df.to_excel(export_path, index=False)
        
        return send_from_directory(
            directory=os.path.dirname(export_path),
            path=os.path.basename(export_path),
            as_attachment=True
        )
    except Exception as e:
        return jsonify({'status': 'error', 'message': str(e)}), 500

@bp.route('/api/reviews', methods=['GET'])
def get_all_reviews():
    try:
        results = (
            db.session.query(Review, ColumnModel.name)
            .join(ColumnModel, Review.column_id == ColumnModel.id)
            .all()
        )
        
        data = [{
            '栏目名称': col_name,
            '姓名': rev.name,
            '稿件': rev.script,
            '播音': rev.broadcast,
            '垫乐': rev.padding,
            '提交时间': rev.submit_time.strftime('%Y-%m-%d %H:%M:%S')
        } for rev, col_name in results]
        
        response = make_response(json.dumps(data, ensure_ascii=False))
        response.headers['Content-Type'] = 'application/json; charset=utf-8'
        return response
    except Exception as e:
        return jsonify({'status': 'error', 'message': str(e)}), 500
